﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Data;

namespace YouDaoNotesCodes
{
    class ConnectSqlServer2017
    {
        /// <summary>
        /// DataRead转DataSet
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static DataSet ConvertDataReaderToDataSet(SqlDataReader reader)//DataRead转DataSet
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table 

                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();

                if (schemaTable != null)
                {
                    // A query returning records was executed 

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table 
                        string columnName = (string)dataRow["ColumnName"]; //+ " // Add the column definition to the data table 
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    // Fill the data table we just created 

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned 

                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }

        /// <summary>
        /// 方法1：连接SQL Service 2017并操作实例cwbase0001中的数据表;方法1 2一致
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <param name="sql"></param>
        public static void ConnectSQL1(string server, string database, string uid, string pwd, string sql)
        {
            //先打开两个类库文件
            //SqlConnection con = new SqlConnection("server=10.110.6.219;database=cwbase0001;uid=lc00019999;pwd=lcbi13");
            SqlConnection con = new SqlConnection();
            con.ConnectionString = string.Format("server={0};database={1};uid={2};pwd={3}", server, database, uid, pwd);
            con.Open();

            //SqlDataAdapter 对象。 用于填充DataSet （数据集）。
            //SqlDataReader 对象。 从数据库中读取流..
            //后面要做增删改查还需要用到 DataSet 对象。

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = sql;
            SqlDataReader dr1 = com.ExecuteReader();//执行SQL语句

            while (dr1.Read())
            {
                //Console.WriteLine(dr1["YuanGongBiaoShi"].ToString();
                for (int i = 0; i < 16; i++)
                {
                    string str = dr1[i].ToString();
                    Console.WriteLine(str);
                }
            }
            dr1.Close();//关闭执行
            con.Close();//关闭数据库
            Console.ReadKey();
        }
        /// <summary>
        /// 方法2：连接SQL Service 2017并操作实例cwbase0001中的数据表;方法1 2一致
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <param name="sql"></param>
        public static void ConnectSQL2(string server, string database, string uid, string pwd, string sql)
        {
            string constr =string.Format( @"server={0};database={1};uid={2};pwd={3}",server,database,uid,pwd);
            using (SqlConnection sqlconn = new SqlConnection(constr))
            {
                string cmdstr = sql;
                using (SqlCommand cmd = new SqlCommand(cmdstr, sqlconn))
                {
                    if (sqlconn.State == ConnectionState.Closed)
                    {
                        sqlconn.Open();
                    }
                    SqlDataReader reader = cmd.ExecuteReader();
                    #region 输出方式1：调用SqlDataReader转DataSet输出相应
                   /* DataSet ds = ConnectSqlServer2017.ConvertDataReaderToDataSet(reader);
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        //string para1 = dr["YuanGongBiaoShi"].ToString();
                        //string para2 = dr["XingMing"].ToString();
                        //string para3 = dr["SuoShuGongSi"].ToString();
                        //Console.WriteLine("\t{0}\t{1}\t{2}\r\n", para1, para2, para3);
                        foreach (DataColumn dc in ds.Tables[0].Columns)
                        {
                            Console.Write(dr[dc].ToString());
                        }
                      
                    }
                    Console.ReadKey();*/
                    #endregion
                    #region 输出方式2：SqlDataReader直接输出
                    if (reader.HasRows)//HasRows判断reader中是否有数据
                    {
                        while (reader.Read())  //Read()方法读取下一条记录，如果没有下一条，返回false,则表示读取完成
                        {
                            string para1 = reader.GetString(0);//Console.Write(reader[0].ToString());//读取此行中第0列的数据
                            string para2 = reader.GetString(1);//string para5=reader["xingming"].ToString();//读取特定xingming列的数据
                            string ppara3 = reader.GetString(2);
                            string para4 = reader.GetString(4);
                            Console.WriteLine("\t{0}\t{1}\t{2}\t{3}\r\n", para1, para2, ppara3, para4);
                        }
                        Console.ReadKey();
                    }
                    #endregion
                    sqlconn.Close();
                }
            }
        }

        //static void Main(string[] args)
        //{
        //    string sql = "SELECT * FROM CRM_YuanGong WHERE SuoShuGongSi IS NOT NULL";
        //    string server = "10.110.6.219";
        //    string database = "cwbase0001";
        //    string uid = "lc00019999";
        //    string pwd = "lcbi13";
        //    ConnectSqlServer2017.ConnectSQL2(server, database, uid, pwd, sql);//连接SQL Service 2017操作CRM_YUANGONG表    
        //}
    }
}
